

package system.imp;
import java.io.DataInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import system.base.ClientDownupfileInfo;
import system.base.CheckSub;
import system.base.ServerDownupfileInfo;
import system.base.WarehouseCategory;
import framework.core.Conditions;
import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.ILoginContext;
import framework.core.LogWriter;
import framework.core.ObjectList;


public class ExcelCheckSubToDB implements GWBaseProcesser {
   public static String doSaveToDB(DataInputStream aFile, DBConnection con, ILoginContext loginContext, String tableName, String warehouseCode,String upDownType)
   throws GWException
{
int tSuccCount = 0;
int tFailCount = 0;
String tFailFile = "0";
HSSFWorkbook tHSSFWorkbook = new HSSFWorkbook();
/*int number = tHSSFWorkbook.getNumberOfNames();
for(int i = 0;i<number;i++){
tableName = tHSSFWorkbook.getSheetAt(i).toString();
}
*/
//开始读取excel文件
HSSFSheet tHSSFSheet = null;
try {
   tHSSFWorkbook = new HSSFWorkbook(aFile);
   tHSSFSheet = tHSSFWorkbook.getSheet(tableName);
   
} catch (IOException e) {
   e.getStackTrace();
   new Exception("批量下载文件失败");
}
ArrayList alist = new ArrayList();
ArrayList tsucceslist = new ArrayList();
ArrayList tErrList = new ArrayList();

//读取并检查Excel明细
int tRowCount = parseExcelCheckSub(alist, tHSSFSheet, loginContext);
//int tRowCount = parseExcelCheckSub(alist,tHSSFSheet);
if (alist.size() > 0) {
   // 保存上传文件到数据库
   for (int i = 0; i < alist.size(); i++) {
       try {
           GWManufactureUploadVO gwManufactureUpload = (GWManufactureUploadVO) alist.get(i);

           CheckSub cb = gwManufactureUpload.getCheckSub();

           // 判断数据库中是否存在该条记录
           // 根据仓库编码和分类编码得到是否在数据库中存在该条信息
           /*
            * Conditions cs = new Conditions();
            * cs.add(WarehouseCategory.WAREHOUSE_CODE,wc.getWarehouseCode());
            * cs.add(WarehouseCategory.CATEGORY_CODE,wc.getCategoryCode());
            * WarehouseCategory warehouseCategory =
            * WarehouseCategory.fetch(con,cs);
            */

           CheckSub CheckSub = cb.fetch(con, cb.getId());
           if (CheckSub == null) {
               // 数据库中不存在的操作:得到ID = GUID值
               CheckSub = CheckSub.fetch();
           }

           // 更新当前一条Excel信息数据库
           CheckSub.setCheckBillCode(cb.getCheckBillCode());
           CheckSub.setCheckBillId(cb.getCheckBillId());
           CheckSub.setPositionCode(cb.getPositionCode());
           CheckSub.setPositionName(cb.getPositionName());
           CheckSub.setProductId(cb.getProductId());
           CheckSub.setProductCode(cb.getProductCode());
           CheckSub.setProductName(cb.getProductName());
           CheckSub.setPositionCount(cb.getPositionCount());
           CheckSub.setActureCount(cb.getActureCount());
           
           CheckSub.setStatus(cb.getStatus());
           CheckSub.setCreateUserId(cb.getCreateUserId());
           CheckSub.setCreateUser(cb.getCreateUser());
           CheckSub.setCreateTime(cb.getCreateTime());
           CheckSub.setLastUpdateUserId(cb.getLastUpdateUserId());
           CheckSub.setLastUpdateUser(cb.getLastUpdateUser());
           CheckSub.setLastUpdateTime(cb.getLastUpdateTime());
           CheckSub.setIsSync(cb.getIsSync());
           CheckSub.save(con);
           tSuccCount++;
       } catch (Exception e) {
           System.out.println(e.getMessage());
           tFailCount++;
       }
   }
               
   // 在循环结束后在客户上传下载表中记录操作信息
   try {
       ServerDownupfileInfo serverDownupfileInfo = new ServerDownupfileInfo();
       ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
       sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
       sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
       sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
       sdfi.setDownloadtyp(upDownType);
       sdfi.setLastUpdateTime(new framework.core.DateUtil().getDateString());
       sdfi.setLastUpdateUser(loginContext.getLoginUserName());
       sdfi.setLastUpdateUserId(loginContext.getLoginUserId());
       sdfi.setParmsstr(tableName);
       sdfi.setDatatableName(tableName);
       sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
       sdfi.setStatus(ConstDefine.STR_YES);
       sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
       sdfi.setWarehouseUincode(warehouseCode);
       sdfi.save(con);
   } catch (Exception e) {
       System.out.println(e.getMessage());    
   }

}
//读取sheet表单
return tSuccCount + "-" + tFailCount + "-" + tRowCount;
}

//end chengbiao.he

/* start chengbiao.he */
//解析Excel文件
private static int parseExcelCheckSub(ArrayList aList, HSSFSheet aHSSFSheet, ILoginContext loginContext) throws GWException {
// private static int parseExcelCheckSub(ArrayList aList,HSSFSheet aHSSFSheet)throws GWException {
int i = 0;
//取得第一行的对象
HSSFRow tFirstRow = aHSSFSheet.getRow(0);

DecimalFormat a = new DecimalFormat("#");
while (true) {
   HSSFRow tHSSFRow = aHSSFSheet.getRow(1 + i);
   boolean flag = false;
   try {

       for (int j = 0; j < 18; j++) {
           if (tHSSFRow == null) {
               break;
           }
           if (tHSSFRow.getCell((short) j) == null) {
               break;
           }
           flag = true;
           break;
       }
       if (!flag) {
           break;
       } else {
           GWManufactureUploadVO tGSManufactureUploadVO = new GWManufactureUploadVO();
           int tResult = 1;
           String tFailReason = "";

           CheckSub cb = new CheckSub();

           for (int j = 0; j <18; j++) {
               // 获得EXCEL首行记录
               String tFiledName = tFirstRow.getCell((short) j).getStringCellValue();
               // 序号
               if (tFiledName.equalsIgnoreCase(CheckSub.ID)) {
                   String tID = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                   cb.setId(tID);
               }
               // 入库单编码
               else if (tFiledName.equalsIgnoreCase(CheckSub.CHECK_BILL_ID)) {
                   String tCheckBillCode = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                   cb.setCheckBillCode(tCheckBillCode);
               }
               // 仓库编码
               else if (tFiledName.equalsIgnoreCase(CheckSub.CHECK_BILL_CODE)) {
                   String tCheckBillId = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                   cb.setCheckBillId(tCheckBillId);
               }
               // 仓库名称
               else if (tFiledName.equalsIgnoreCase(CheckSub.POSITION_CODE)) {
                   //int tWarehouseName = tHSSFRow.getCell((short)j).getCellType();
                   String tPositionCode = PoiUtil.getCellValue(tHSSFRow, j, "");
                   cb.setPositionCode(tPositionCode);

               }
               // 供应商编码
               else if (tFiledName.equalsIgnoreCase(CheckSub.POSITION_NAME)) {
                   String tPositionName = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                   cb.setPositionName(tPositionName);
               }
               // 供应商名称
               else if (tFiledName.equalsIgnoreCase(CheckSub.PRODUCT_ID)) {
                   String tProductId = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue().trim();
                   cb.setProductId(tProductId);

               }
               // 对方仓库编码
               else if (tFiledName.equalsIgnoreCase(CheckSub.PRODUCT_CODE)) {
                   String tProductCode = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setProductCode(tProductCode);
               }

               // 对方仓库名称
               else if (tFiledName.equalsIgnoreCase(CheckSub.PRODUCT_NAME)) {
                   String tProductName = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getNumericCellValue());
                   cb.setProductName(tProductName);
               }
               // 入库时间
               else if (tFiledName.equalsIgnoreCase(CheckSub.POSITION_COUNT)) {
                  // String tPositionCount = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   //double tPositionCount = tHSSFRow.getCell(((short)j).
                  double tPositionCount = tHSSFRow.getCell((short)j).getNumericCellValue();
                   cb.setPositionCount(tPositionCount);
               }
               // 入库类型
               else if (tFiledName.equalsIgnoreCase(CheckSub.ACTURE_COUNT)) {
                  // String tActureCount = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   double tActureCount = tHSSFRow.getCell((short)j).getNumericCellValue();
                   cb.setActureCount(tActureCount);
               }
               
               // 是否有效
               else if (tFiledName.equalsIgnoreCase(CheckSub.STATUS)) {
                   String tStatus = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue().trim();
                   cb.setStatus(tStatus);

               }
               // 创建用户ID
               else if (tFiledName.equalsIgnoreCase(CheckSub.CREATE_USER_ID)) {
                   String tCreateUserId = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setCreateUserId(tCreateUserId);
               }

               // 创建用户
               else if (tFiledName.equalsIgnoreCase(CheckSub.CREATE_USER)) {
                   String tCreateUser = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getNumericCellValue());
                   cb.setCreateUser(tCreateUser);
               }
               // 单证创建时间
               else if (tFiledName.equalsIgnoreCase(CheckSub.CREATE_TIME)) {
                   String tCreateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setCreateTime(tCreateTime);
               }
               // 最后修改人序号
               else if (tFiledName.equalsIgnoreCase(CheckSub.LAST_UPDATE_USER_ID)) {
                   String tLastUpdateUserId = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setLastUpdateUserId(tLastUpdateUserId);
               }
               // 最后修改人
               else if (tFiledName.equalsIgnoreCase(CheckSub.LAST_UPDATE_USER)) {
                   String tLastUpdateUser = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                   cb.setLastUpdateUser(tLastUpdateUser);
               }
               // 最后修改时间
               else if (tFiledName.equalsIgnoreCase(CheckSub.LAST_UPDATE_TIME)) {
                   String tLastUpdateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setLastUpdateTime(tLastUpdateTime);
               }
            // 是否已同步
               else if (tFiledName.equalsIgnoreCase(CheckSub.IS_SYNC)) {
                   String IsSync = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                   cb.setIsSync(IsSync);
               }
               

           }
           tGSManufactureUploadVO.setCheckSub(cb);
           // tGSManufactureUploadVO.setFailure_reason(tFailReason);
           tGSManufactureUploadVO.setInsert_result(String.valueOf(tResult));
           tGSManufactureUploadVO.setRowNumber(i + 1);
           aList.add(tGSManufactureUploadVO);
           i++;
       }
   } catch (RuntimeException e) {
       LogWriter.error("****Manufacture BatchImport RuntimeEx :[" + e.getMessage() + "]");
       throw new GWException("导入数据中某一列数据类型不符合规定.");
   } catch (Error e) {
       LogWriter.error("****Manufacture BatchImport ErroeEx :[" + e.getMessage() + "]");
       throw new GWException(e.getMessage());
   } catch (Exception e) {
       LogWriter.error("****Manufacture BatchImport Ex :[" + e.getMessage() + "]");
       throw new GWException(e.getMessage());
   }
}
return i ;
}

//根据参数创建要下载的Excel文件
/**
* return 创建文件的路径
* 创建失败返回 "Error"
*/
/*
public static String createExcelByDownLoadCheckSub(DBConnection con,String serverPath,String tableName,String warehouseCode,String type,String lastupdatetime)
{
String strFilePath = "";
try{
   //根据参数查询数据
   Conditions cs = new Conditions();
   if (type.equals(ConstDefine.EXCELFILE_LOAD_TYPE_TIME))
   {
       String strDate = framework.core.DateUtil.formatToLongDateString(lastupdatetime, new framework.core.DateUtil().getFullString());
       cs.add(WarehouseCategory.LAST_UPDATE_TIME +">= ?",Conditions.CUSTOM,strDate);
   }
   ObjectList list = CheckSub.fetch().search(con, cs);            
   
   //Excel生成文件保存到指定位置
   try{    
       HSSFWorkbook tWb = new HSSFWorkbook();
       HSSFSheet tSheet = tWb.createSheet(tableName);
       HSSFRow tRow = null;
  
       //表头行
       tRow = tSheet.createRow((short)0);

       HSSFCell c1 = PoiUtil.createCell(tWb,tRow,(short)0,CheckSub.ID);
       HSSFCell c2 = PoiUtil.createCell(tWb,tRow,(short)1,CheckSub.IN_BILL_CODE);
       HSSFCell c3 = PoiUtil.createCell(tWb,tRow,(short)2,CheckSub.WAREHOUSE_CODE);
       HSSFCell c4 = PoiUtil.createCell(tWb,tRow,(short)3,CheckSub.WAREHOUSE_NAME);
       HSSFCell c5 = PoiUtil.createCell(tWb,tRow,(short)4,CheckSub.PROVIDER_CODE);
       HSSFCell c6 = PoiUtil.createCell(tWb,tRow,(short)5,CheckSub.PROVIDER_NAME);
       HSSFCell c7 = PoiUtil.createCell(tWb,tRow,(short)6,CheckSub.OTHER_WAREHOUSE_CODE);
       HSSFCell c8 = PoiUtil.createCell(tWb,tRow,(short)7,CheckSub.OTHER_WAREHOUSE_NAME);
       HSSFCell c9 = PoiUtil.createCell(tWb,tRow,(short)8,CheckSub.CREATE_TIME);
       HSSFCell c10 = PoiUtil.createCell(tWb,tRow,(short)9,CheckSub.IN_TYPE);
       HSSFCell c11 = PoiUtil.createCell(tWb,tRow,(short)10,CheckSub.IN_MONEY);
       HSSFCell c12 = PoiUtil.createCell(tWb,tRow,(short)11,CheckSub.SEND_BILL_CODE);
       HSSFCell c13 = PoiUtil.createCell(tWb,tRow,(short)12,CheckSub.REMARK);
       HSSFCell c14 = PoiUtil.createCell(tWb,tRow,(short)13,CheckSub.BALANCE_FLAG);
       HSSFCell c15 = PoiUtil.createCell(tWb,tRow,(short)14,CheckSub.BALANCE_USER_ID);
       HSSFCell c16 = PoiUtil.createCell(tWb,tRow,(short)15,CheckSub.BALANCE_USER);
       HSSFCell c17 = PoiUtil.createCell(tWb,tRow,(short)16,CheckSub.BALANCE_TIME);
       HSSFCell c18= PoiUtil.createCell(tWb,tRow,(short)17,CheckSub.STATUS);
       HSSFCell c19 = PoiUtil.createCell(tWb,tRow,(short)18,CheckSub.CREATE_USER_ID);
       HSSFCell c20 = PoiUtil.createCell(tWb,tRow,(short)19,CheckSub.CREATE_USER);
       HSSFCell c21 = PoiUtil.createCell(tWb,tRow,(short)20,CheckSub.CREATE_TIME);
       HSSFCell c22 = PoiUtil.createCell(tWb,tRow,(short)21,CheckSub.LAST_UPDATE_USER_ID);
       HSSFCell c23 = PoiUtil.createCell(tWb,tRow,(short)22,CheckSub.LAST_UPDATE_USER);
       HSSFCell c24 = PoiUtil.createCell(tWb,tRow,(short)23,CheckSub.LAST_UPDATE_TIME);
       HSSFCell c25 = PoiUtil.createCell(tWb,tRow,(short)24,CheckSub.IS_SYNC);

       short index = 0 ; //当前所在行号          
       short index_b = 3;
       for(int i=0;i<list.size();i++){
           CheckSub cb = (CheckSub)list.get(i);    
 
           //创建一行
           tRow = tSheet.createRow((short)(index+1));
           
           PoiUtil.createCell(tWb,tRow,(short)0,cb.getId() + "");
           PoiUtil.createCell(tWb,tRow,(short)1,cb.getInBillCode() + "");
           PoiUtil.createCell(tWb,tRow,(short)2,cb.getWarehouseCode() + "");
           PoiUtil.createCell(tWb,tRow,(short)3,cb.getWarehouseName() + "");
           PoiUtil.createCell(tWb,tRow,(short)4,cb.getProviderCode() + "");
           PoiUtil.createCell(tWb,tRow,(short)5,cb.getProviderName() + "");
           PoiUtil.createCell(tWb,tRow,(short)6,cb.getOtherWarehouseCode() + "");
           PoiUtil.createCell(tWb,tRow,(short)7,cb.getWarehouseName() + "");
           PoiUtil.createCell(tWb,tRow,(short)8,cb.getInTime() + "");
           PoiUtil.createCell(tWb,tRow,(short)9,cb.getInType() + "");
           PoiUtil.createCell(tWb,tRow,(short)10,cb.getInMoney() + "");
           PoiUtil.createCell(tWb,tRow,(short)11,cb.getSendBillCode() + "");
           PoiUtil.createCell(tWb,tRow,(short)12,cb.getRemark() + "");
           PoiUtil.createCell(tWb,tRow,(short)13,cb.getBalanceFlag() + "");
           PoiUtil.createCell(tWb,tRow,(short)14,cb.getBalanceUserId() + "");
           PoiUtil.createCell(tWb,tRow,(short)15,cb.getBalanceUser() + "");
           PoiUtil.createCell(tWb,tRow,(short)16,cb.getBalanceTime() + "");
           PoiUtil.createCell(tWb,tRow,(short)17,cb.getStatus() + "");
           PoiUtil.createCell(tWb,tRow,(short)18,cb.getCreateUserId() + "");
           PoiUtil.createCell(tWb,tRow,(short)19,cb.getCreateUser() + "");
           PoiUtil.createCell(tWb,tRow,(short)20,cb.getCreateTime() + "");
           PoiUtil.createCell(tWb,tRow,(short)21,cb.getLastUpdateUserId() + "");
           PoiUtil.createCell(tWb,tRow,(short)22,cb.getLastUpdateUser() + "");
           PoiUtil.createCell(tWb,tRow,(short)23,cb.getLastUpdateTime() + "");
           PoiUtil.createCell(tWb,tRow,(short)24,cb.getIsSync() + "");


           index++;
       }
       String strTime = "";
       try{
           strTime = system.imp.DateUtil.getCurrTime();
       }
       catch(Exception e)
       {
       }
       
       String fileName;
       String filePath = serverPath + ConstDefine.EXCELFILE_SAVECREATEFILE_PATH;
       fileName ="download_" + strTime + "_" + tableName + ".xls";
       FileOutputStream fileOut = new FileOutputStream(filePath+fileName);
       tWb.write(fileOut);
       
       //返回下载路径
       strFilePath = ConstDefine.EXCELFILE_DOWNLOADCREATEFILE_PATH + fileName;
       
   }catch(Exception ex){
       strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
   }
   
}
catch(Exception e)
{
   strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
}


return strFilePath;
}
*/  
}

/* ExcelInoutToDB.java
*
* Project/Product: GW
*
* Modify Information:
* =============================================================================
*   Author Date       Description
*   ---------- ---------- ---------------------------------------------------
*   Administrator  2008-2-20   First release
*
* Licensed Materials - Property of APEP
* =============================================================================
*    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
*    All Rights Reserved.
*
*    This software is the confidential and proprietary information of
*    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
*    You shall not disclose such Confidential Information and shall use it only
*    in accordance with the terms of the license agreement you entered into 
*    with Shanghai Auto Mart E-Procurement Co., Ltd.
*
* Noties:
* =============================================================================
* 
*/




